package com.deng.etl.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.deng.etl.common.IEtlInterface;
import com.deng.etl.common.EtlLog;
//制空
public class ImportZhuYuan_count extends ImportBase implements IEtlInterface {
	
	
public static String COLUMNS=" 科室ID,科室名字,year(出院时间) as year,month(出院时间) as month,count(出院时间) as count ";
	
	public static String SELECT="select "+COLUMNS+"  from neusoft.JIXIAO_ZHUYUANINFO " +
			"where 出院时间>=? and 出院时间<? group by 科室ID,科室名字,year(出院时间),month(出院时间)";
	
	public static String INSERT="insert into t_org_organization_workload_month " +
			"(stat_time,org_operate_organization_id,org_operate_organization_name," +
			"create_time,ip_in_count,ip_out_count," +
			"op_count,cure_count,bed_use_rate,bed_use_count,bed_open_count) " +
			"values(?,?,?,?,null,?,null,null,null,null,null)";
	
	public static String UPDATE="update t_org_organization_workload_month set ip_out_count=(case when ip_out_count is null then 0 else ip_out_count end)+? " +
			"where stat_time=? and org_operate_organization_id=?";	
	
	public static String DELETE="update t_org_organization_workload_month set ip_out_count=null where stat_time>=? and stat_time<?";
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ImportZhuYuan_count im=new ImportZhuYuan_count();

		
	}

	public boolean checkExist(Connection conn,java.sql.Date date,String id){
		boolean ret=false;
		try {
			System.out.println("---------checkExist-------"+date.toLocaleString()+";"+id);
			PreparedStatement pst =conn.prepareStatement("select stat_time from " +
					"t_org_organization_workload_month where stat_time=? and org_operate_organization_id=?");
			pst.setDate(1, date);
			pst.setString(2, id);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				ret=true;
			}
			rs.close();
			pst.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	
	
	public void insertData(Connection sourceConn,Connection targetConn,String name,String fromDate,String toDate) {
		System.out.println("从："+fromDate+"   到："+toDate);
		

		Date from=null;
		Date to=null;
		try {
			from = format_s.parse(fromDate);
			to=format_s.parse(toDate);
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		System.out.println("-SELECT-------"+SELECT);
		
		try {
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			PreparedStatement pst=sourceConn.prepareStatement(SELECT);
			PreparedStatement pst_insert=targetConn.prepareStatement(INSERT);
			PreparedStatement pst_update=targetConn.prepareStatement(UPDATE);
			pst.setDate(1, getSqlDate(from));
			pst.setDate(2, getSqlDate(to));
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				
				String or_code=rs.getString("科室ID");
				String or_name=rs.getString("科室名字");
				java.sql.Date date=new java.sql.Date(rs.getInt("year")-1900,rs.getInt("month")-1,1);
				boolean exist=checkExist(targetConn,date, or_code);
				//修改
				if(exist){
					pst_update.setInt(1, rs.getInt("count"));
					pst_update.setDate(2, date);
					pst_update.setString(3, or_code);
					update(pst_update);
				}
				else{//新增 
					pst_insert.setDate(1, date);
					pst_insert.setString(2, or_code);
					pst_insert.setString(3, or_name);
					Date date1=new Date();
					pst_insert.setTimestamp(4, getTimestamp(date1));
					pst_insert.setInt(5, rs.getInt("count"));
					
					insert(pst_insert);
				}
				
				
			}
			rs.close();
			pst.close();
			pst_insert.close();
			pst_update.close();
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public void deleteData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {
		// TODO Auto-generated method stub
		System.out.println("从："+fromDate+"   到："+toDate);
		
		Date from=null;
		Date to=null;

		try {
			from = format_s.parse(fromDate);
			to = format_s.parse(toDate);
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try{
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			PreparedStatement pst_delete=targetConn.prepareStatement(DELETE);
			java.sql.Date date=new java.sql.Date(from.getYear(),from.getMonth(),1);
			java.sql.Date date1=new java.sql.Date(to.getYear(),to.getMonth(),1);
			pst_delete.setDate(1,date);
			pst_delete.setDate(2,date1);
			pst_delete.executeUpdate();
			pst_delete.close();
				
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		
	}

	public void updateData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {

		System.out.println("从："+fromDate+"   到："+toDate);

		try {
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志

			deleteData(sourceConn, targetConn,name, fromDate, toDate);
			insertData(sourceConn, targetConn,name, fromDate, toDate);
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}
	
//	-----------------------------------入院时间----------------
	
//	public static String COLUMNS=" 科室ID,科室名字,year(入院时间) as year,month(入院时间) as month,count(入院时间) as count ";
//	
//	public static String SELECT="select "+COLUMNS+"  from neusoft.JIXIAO_ZHUYUANINFO " +
//			"where 入院时间>=? and 入院时间<? group by 科室ID,科室名字,year(入院时间),month(入院时间)";
//	
//	public static String INSERT="insert into t_org_organization_workload_month " +
//			"(stat_time,org_operate_organization_id,org_operate_organization_name," +
//			"create_time,ip_in_count,ip_out_count," +
//			"op_count,cure_count,bed_use_rate,bed_use_count,bed_open_count) " +
//			"values(?,?,?,?,null,?,null,null,null,null,null)";
//	
//	public static String UPDATE="update t_org_organization_workload_month set ip_out_count=(case when ip_out_count is null then 0 else ip_out_count end)+? " +
//			"where stat_time=? and org_operate_organization_id=?";	
//	
//	public static String DELETE="update t_org_organization_workload_month set ip_out_count=null where stat_time>=? and stat_time<?";
//	/**
//	 * @param args
//	 */
//	public static void main(String[] args) {
//		// TODO Auto-generated method stub
//		ImportZhuYuan_count im=new ImportZhuYuan_count();
//
//		
//	}
//
//	public boolean checkExist(Connection conn,java.sql.Date date,String id){
//		boolean ret=false;
//		try {
//			System.out.println("---------checkExist-------"+date.toLocaleString()+";"+id);
//			PreparedStatement pst =conn.prepareStatement("select stat_time from " +
//					"t_org_organization_workload_month where stat_time=? and org_operate_organization_id=?");
//			pst.setDate(1, date);
//			pst.setString(2, id);
//			ResultSet rs=pst.executeQuery();
//			if(rs.next()){
//				ret=true;
//			}
//			rs.close();
//			pst.close();
//		
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return ret;
//	}
//	
//	
//	public void insertData(Connection sourceConn,Connection targetConn,String name,String fromDate,String toDate) {
//		System.out.println("从："+fromDate+"   到："+toDate);
//		
//
//		Date from=null;
//		Date to=null;
//		try {
//			from = format_s.parse(fromDate);
//			to=format_s.parse(toDate);
//		} catch (ParseException e1) {
//			// TODO Auto-generated catch block
//			e1.printStackTrace();
//		}
//		
//		System.out.println("-SELECT-------"+SELECT);
//		
//		try {
//			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
//			PreparedStatement pst=sourceConn.prepareStatement(SELECT);
//			PreparedStatement pst_insert=targetConn.prepareStatement(INSERT);
//			PreparedStatement pst_update=targetConn.prepareStatement(UPDATE);
//			pst.setDate(1, getSqlDate(from));
//			pst.setDate(2, getSqlDate(to));
//			ResultSet rs=pst.executeQuery();
//			while(rs.next()){
//				
//				String or_code=rs.getString("科室ID");
//				String or_name=rs.getString("科室名字");
//				java.sql.Date date=new java.sql.Date(rs.getInt("year")-1900,rs.getInt("month")-1,1);
//				boolean exist=checkExist(targetConn,date, or_code);
//				//修改
//				if(exist){
//					pst_update.setInt(1, rs.getInt("count"));
//					pst_update.setDate(2, date);
//					pst_update.setString(3, or_code);
//					update(pst_update);
//				}
//				else{//新增 
//					pst_insert.setDate(1, date);
//					pst_insert.setString(2, or_code);
//					pst_insert.setString(3, or_name);
//					Date date1=new Date();
//					pst_insert.setTimestamp(4, getTimestamp(date1));
//					pst_insert.setInt(5, rs.getInt("count"));
//					
//					insert(pst_insert);
//				}
//				
//				
//			}
//			rs.close();
//			pst.close();
//			pst_insert.close();
//			pst_update.close();
////			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
//			
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	}
//	
//	
//	public void deleteData(Connection sourceConn, Connection targetConn,
//			String name, String fromDate, String toDate) {
//		// TODO Auto-generated method stub
//		System.out.println("从："+fromDate+"   到："+toDate);
//		
//		Date from=null;
//		Date to=null;
//
//		try {
//			from = format_s.parse(fromDate);
//			to = format_s.parse(toDate);
//		} catch (ParseException e1) {
//			// TODO Auto-generated catch block
//			e1.printStackTrace();
//		}
//		try{
//			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
//			PreparedStatement pst_delete=targetConn.prepareStatement(DELETE);
//			java.sql.Date date=new java.sql.Date(from.getYear(),from.getMonth(),1);
//			java.sql.Date date1=new java.sql.Date(to.getYear(),to.getMonth(),1);
//			pst_delete.setDate(1,date);
//			pst_delete.setDate(2,date1);
//			pst_delete.executeUpdate();
//			pst_delete.close();
//				
////			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
//		}
//		catch(SQLException e){
//			e.printStackTrace();
//		}
//		
//	}
//
//	public void updateData(Connection sourceConn, Connection targetConn,
//			String name, String fromDate, String toDate) {
//
//		System.out.println("从："+fromDate+"   到："+toDate);
//
//		try {
//			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
//
//			deleteData(sourceConn, targetConn,name, fromDate, toDate);
//			insertData(sourceConn, targetConn,name, fromDate, toDate);
////			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
//			
//		} catch (Exception e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	
//	}
}
